package com.simoniu.transactiondemo;

import com.simoniu.entity.Students;
import com.simoniu.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.ArrayList;
import java.util.List;

public class TransactionDemo {

    //做批量新增，出现异常后回顾。
    public void addBatchRollback(List<Students> studentsList) {
        String sql = "insert into students (sname,gender) values (?,?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        try  {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            //开启事务
            conn.setAutoCommit(false);

            for(Students s: studentsList){
                pstmt.setString(1,s.getSname());
                pstmt.setString(2,s.getGender());
                pstmt.addBatch();
            }
            pstmt.executeBatch(); //执行批处理。
            //conn.commit(); //提交事务；

        }catch(Exception ex){
            ex.printStackTrace();
            try {
                conn.rollback();
                //conn.commit();
            }catch(Exception e){
                e.printStackTrace();
            }

        }finally{
            try {
                //提交事务
                conn.commit();
                conn.setAutoCommit(true);
            }catch(SQLException e){
                e.printStackTrace();
            }
        }

    }

    //做批量新增，出现异常后回顾到保存点。
    public void addBatchRollbackToSavepoint(List<Students> studentsList) {
        String sql = "insert into students (sname,gender) values (?,?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        Savepoint savepoint = null;
        try  {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            //开启事务
            conn.setAutoCommit(false);

            for(Students s: studentsList){
                pstmt.setString(1,s.getSname());
                pstmt.setString(2,s.getGender());
                pstmt.addBatch();
            }
            pstmt.executeBatch(); //执行批处理。
            //执行一下批处理，目的把东吸毒南帝北丐四个添加成功。
            //设置一个保存点。
            savepoint = conn.setSavepoint("sp1");

            //把'南帝' 的性别改为'公'，违反了检查约束。肯定不成功。
            sql = "update students set gender = ? where sname = '南帝';";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1,"女");
            pstmt.executeUpdate(); //这里肯定会抛出异常。

        }catch(Exception ex){
            ex.printStackTrace();
            try {
                //conn.rollback();
                conn.rollback(savepoint);
                //conn.commit();
            }catch(Exception e){
                e.printStackTrace();
            }

        }finally{
            try {
                //提交事务
                conn.commit();
                conn.setAutoCommit(true);
            }catch(SQLException e){
                e.printStackTrace();
            }
        }

    }

    public static void main(String[] args) {
        List<Students> studentsList = new ArrayList<Students>();
        studentsList.add(new Students(100, "黄药师", "男"));
        studentsList.add(new Students(100, "欧阳锋", "男"));
        studentsList.add(new Students(100, "南帝", "男"));
        studentsList.add(new Students(100, "洪七公", "男"));

        TransactionDemo demo = new TransactionDemo();
        //demo.addBatchRollback(studentsList);
        demo.addBatchRollbackToSavepoint(studentsList);
    }
}
